<?php
/**
 * 通用的 QueryScope 过滤方法
 *
 * Author: Zeon
 * Date: 2017-12-22
 * Created by Panxsoft.
 */
namespace App\Models\Concerns;

use App\Services\Utils\TimeHelper;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Relations\HasMany;

/**
 * @method static self columnsKeywordsOrLike(array $columns_keyword, bool $left_match = true)
 * @method static self columnsKeywordOrLike(array $columns, ?string $keyword, bool $left_match = true)
 * @method static self columnsKeywordInstr(array $columns_keyword)
 *
 * @method static self orderBys(array $order_bys = null, array $default_order_bys = null)
 * @method static self orderByDefaultLatest(?string $order_column = 'created_at', ?string $sort = 'DESC')
 *
 * @method static self filterToday(string $order_column = 'created_at')
 * @method static self filterTimeRange(array $time_range = null, string $column = 'created_at')
 * @method static self filterLimitDate(string $started_at = 'started_at', string $ended_at = 'ended_at')
 *
 * @method static self filterWhen(string $column, ?string $value)
 * @method static self filterWhenIn(string $column, ?array $values)
 *
 * @method static self|HasMany filterLimitSpecifiedRangeDate()
 */
trait ScopeFilterable
{
    /**
     * @used-by filterTimeRange - 通用的时间过滤方法, 传入无效值则不过滤，传入的时间数组无效则从UNIX开始或结束时间过滤
     *
     * @example
     *      (new Model)->filterTimeRange(['1970-10-10', '2013-11-10'])
     *      (new Model)->filterTimeRange(['00000', '2013-11-10'], 'updated_at')
     *      (new Model)->filterTimeRange([null, null])
     *
     * @param Builder $query
     * @param array|string[2]|null $time_range
     * @param string $column
     * @return Builder
     */
    public function scopeFilterTimeRange(Builder $query, array $time_range = null, $column = 'created_at')
    {
        if (!$time_range) return $query;

        list($start, $end) = $time_range;

        return $query->whereBetween($column, [
            TimeHelper::tryToCarbonOrUnixStart($start),
            TimeHelper::tryToCarbonOrUnixEnd($end),
        ]);
    }

    /**
     * @used-by orderBys - 多栏排序
     *
     * @example $newQuery()->orderBys([
     *      ['order_by' => 'amount', 'sort' => 'asc'],
     *      ['order_by' => 'created_at', 'sort' => 'desc'],
     * ])
     *
     * You may use like use an default sort if front end didn't provide an order_bys param
     *
     * User::orderBys(null, [
     *      ['order_by' => 'created', 'sort' => 'desc'],
     * ])
     * ->limit(2)
     * ->pluck('created_at','id')
     *
     * @param Builder $query
     * @param array[]|null $order_bys
     * @param array|null $default_order_bys
     *
     * @return Builder
     */
    public function scopeOrderBys(Builder $query, array $order_bys = null, array $default_order_bys = null)
    {
        if (! $order_bys) {
            if (! $default_order_bys) {
                return $query;
            } else {
                /** @var self $query */
                return $query->orderBys($default_order_bys);
            }
        }

        $order_by = array_shift($order_bys);

        /** @var self $self */
        $self = $query->orderBy($order_by['order_by'], $order_by['sort']);

        // apply and call recursive
        return $self->orderBys($order_bys);
    }

    /**
     * @used-by columnsKeywordsOrLike - 多栏多关键词 orLike 搜索, 如关键词相同请使用 columnsKeywordOrLike
     *
     * @example $newQuery()->columnsKeywordsOrLike([
     *      'title' => 'aa产品',
     *      'desc' => 'bb产品',
     *      'other' =>  null , // 这个会跳过
     * ])
     *
     * @param Builder $query
     * @param array|null $col_keyword
     * @param bool $left_match
     * @return Builder
     */
    public function scopeColumnsKeywordsOrLike(Builder $query, array $col_keyword, bool $left_match = true)
    {
        /**
         * 默认左 % 匹配，但这样不能使用索引
         */
        $left_match_symbol = $left_match ? '%' : '';

        return $query->where(function (Builder $query) use ($col_keyword, $left_match_symbol) {
            foreach ($col_keyword as $column => $keyword) {
                if ((string) $keyword) {
                    $query->orWhere($column, 'LIKE', $left_match_symbol . $keyword . '%');
                }
            }
        });
    }

    /**
     * @used-by columnsKeywordOrLike - 多栏单关键词 orLike 搜索
     *
     * @example $newQuery()->columnsKeywordOrLike(['title', 'desc', 'other'], 'xx产品')
     *
     * @param Builder|static $query
     * @param array $columns
     * @param null|string $keyword
     * @param bool $left_match
     *
     * @return ScopeFilterable
     */
    public function scopeColumnsKeywordOrLike(Builder $query, array $columns, ?string $keyword, bool $left_match = true)
    {
        if (! ((string) $keyword)) {
            return $query;
        }

        $col_keys = [];
        foreach ($columns as $column) {
            $col_keys[$column] = $keyword;
        }

        return $query->columnsKeywordsOrLike($col_keys, $left_match);
    }

    /**
     * @used-by columnsKeywordInstr - 多栏 instr 搜索
     *
     * @link https://blog.csdn.net/ljj2312/article/details/78217752
     * @link https://blog.csdn.net/zmzwll1314/article/details/51742411
     *
     * @example $newQuery()->columnsKeywordInstr([
     *      'title' => 'xx产品',
     *      'desc' => 'xx产品',
     *      'other' =>  null , // 这个会跳过
     * ])
     *
     * @param Builder $query
     * @param array|null $col_keyword
     * @return Builder
     */
    public function scopeColumnsKeywordInstr(Builder $query, array $col_keyword)
    {
        return $query->where(function (Builder $query) use ($col_keyword) {
            foreach ($col_keyword as $column => $keyword) {
                if ($keyword) {
                    /**
                     * 此处 $column 存在注入风险，需要严格限制传入 column 的值是合法栏名称
                     *
                     * 如： Product::ColumnsKeywordInstr(["spu_code`, '1')>0 or instr(`id" => '-04'])->get()
                     *
                     * 加反引号就输入 products.id 是错的，没有 `products.id` 这种写法， 只能写 `products`.`id`
                     * Mysql 的反引号是用来区别保留字的，比如 DESC 是保留字， 但 products 表上有 desc 一栏查询时
                     * 不能写 select desc from products 只能写 select `desc` from products
                     */
                    $query->orWhereRaw("instr(`$column`, ?)>0", [$keyword]);
                }
            }
        });
    }

    /**
     * @used-by orderByDefaultLatest - 单栏排序（默认按创建日期倒序）
     *
     * @param Builder $query
     * @param string|null $order_column
     * @param string|null $sort
     *
     * @return Builder
     */
    public function scopeOrderByDefaultLatest(Builder $query, ?string $order_column, ?string $sort)
    {
        // ?string 表示 string or null, 但函数调用必须传入参数
        // $param = 'default' 表示调用函数不传值时的默认值，如果调用函数传入 null, 是不会用默认值 'default' 的
        // $val ?? 'default' 与 isset($val) ? $val : 'default' 行为是一样的
        return $query->orderBy($order_column ?: 'created_at', $sort ?: 'DESC');
    }

    /**
     * @used-by filterToday - 查找今天的数据
     *
     * @param Builder $query
     * @param string|null $order_column
     *
     * @return Builder
     */
    public function scopeFilterToday(Builder $query, string $order_column = 'created_at')
    {
        return $query->whereDate($order_column, now()->toDateString());
    }

    /**
     * @used-by filterLimitDate - 限制当前时间在指定栏的日期区间段内
     *
     * @param Builder $query
     * @param string $started_at
     * @param string $ended_at
     *
     * @return Builder
     */
    public function scopeFilterLimitDate(Builder $query,
                                         string $started_at = 'started_at',
                                         string $ended_at = 'ended_at')
    {
        $now = now();

        return $query->where([
            [$started_at, '<=', $now],
            [$ended_at, '>=', $now],
        ]);
    }

    /**
     * @used-by filterWhen - 当值非空时查找对应字段
     *
     * @param Builder $query
     * @param string $column
     * @param string $value
     *
     * @return void
     */
    public function scopeFilterWhen(Builder $query, string $column, ?string $value)
    {
        if (! is_null($value)) {
            $query->where($column, $value);
        }
    }

    /**
     * @used-by filterWhenIn - 当值非空时 whereIn 查找对应字段
     *
     * @param Builder $query
     * @param string $column
     * @param array|null $values
     *
     * @return void
     */
    public function scopeFilterWhenIn(Builder $query, string $column, ?array $values)
    {
        if (! is_null($values)) {
            $query->whereIn($column, $values);
        }
    }

    /**
     * @used-by filterLimitSpecifiedRangeDate -  限制当前时间在指定范围的日期区间段内
     * @param Builder $query
     */
    public function scopeFilterLimitSpecifiedRangeDate(Builder $query)
    {
        $now = now();

        $query
            // 0000-00-00 本来就小于 now 不用多写一个 = 判断
            ->where('date_start', '<', $now)
            ->where(function (Builder $query) use ($now) {
                $query
                    ->where('date_end', '0000-00-00')
                    ->orWhere('date_end', '>', $now);
            });
    }
}
